﻿using HCQ2_Common.SQL;
using HCQ2_Model;
using HCQ2_Model.VRDD.InputDto._20220627;
using HCQ2_Model.VRDD.OutputDto._20220627;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_ItemCodeMenumDAL
    {
        /// <summary>
        /// 获取PointsRule积分规则列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputGetPointsRuleList> GetPointsRuleList(InputGetUserPointsBalance param,int userid)
        {
            sb.Clear();
            sb.Append($@"select name,code,qValue,note,image,sum,updateDate from (
            select code_name as name, code_value as code,code_order,
           qValue=convert (int,isnull((case when code_value='UserSign'  then (select numValue=(select top 1 numValue from T_ItemCode where item_code='SignRewardRule')
	        from T_ItemCodeMenum where code_value='UserSign')
	        else
	        numValue
	        end),0)),
            code_note as note,logoUrl as image,
           sum=Convert(int,isnull((case when code_name='UserSign' then CAST((select top 1 sigcomDay from T_VRUserSignIn where createId={userid} order by sigDate) as int)
            when code_value='BrowseVideo' then (select totalScore from T_VRScoreSourceMain where itemType=code_value)/convert(int,isnull(code_type,0))
            when code_value='PostVideo'  then (select count(*) from T_VRScoreSourceDetail where itemType=code_value and user_id={userid})
            when code_value='Shopping' then (select totalScore from T_VRScoreSourceMain where itemType=code_value and user_id={userid}) 
            when code_value='SendInvitation'  then (select count(*) from T_VRScoreSourceDetail where itemType='SendInvitation' and user_id={userid})
            when code_value='Registration'  then (select top 1 totalScore from  T_VRScoreSourceDetail where itemType='Registration' and user_id={userid} order by createDate desc)
            else null
            end),0)),
            updateDate=convert(nvarchar(100),(select top 1 createDate from T_VRScoreSourceDetail order by createDate desc),120)
            from T_ItemCodeMenum   where item_id=(select item_id from T_ItemCode where item_code='PointsRule') 
	        )a where a.qValue>0  order by code_order asc");
            return SqlHelper.ExecuteList<OutputGetPointsRuleList>(sb.ToString());
        }
        /// <summary>
        /// 获取签到奖励规则列表
        /// </summary>
        /// <returns></returns>
        public List<OutputGetPointsRuleList> GetSignRewardRuleList()
        {
            sb.Clear();
            sb.Append($@" select code_name as name,code_value as code,convert(int, code_type) as qValue,convert(int,code_type) as sum, code_note as note,logoUrl as image,
	         updateDate=convert(nvarchar(100),(select top 1 createDate from T_VRScoreSourceDetail order by createDate desc),120)
	         from  T_ItemCodeMenum where item_id= (select item_id from T_ItemCode where item_code='SignRewardRule')");
            return SqlHelper.ExecuteList<OutputGetPointsRuleList>(sb.ToString());
        }

        /// <summary>
        /// 获取连续签到天数
        /// </summary>
        /// <returns></returns>
        public int signInNum(DateTime date, int userid)
        {

            sb.Clear();
            sb.Append($@"select top 1 sigcomDay from T_VRUserSignIn where 
	   CONVERT(date,sigDate)=CONVERT(date, dateadd(day,-1,'{date}')) 
	   and createId={userid}
	   order by sigDate desc");
            return SqlHelper.ExecuteScalarInt32(sb.ToString());
        }
        /// <summary>
        /// 查询是否签到
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public T_VRUserSignIn GetUserSignIn(DateTime dateTime, int userid,string proType)
        {
            sb.Clear();
            sb.Append($@"select * from T_VRUserSignIn where createId={userid} and CONVERT(date,sigDate)=CONVERT(date,'{dateTime}') and proType='{proType}'");
            return SqlHelper.ExecuteList<T_VRUserSignIn>(sb.ToString()).FirstOrDefault();
        }

        /// <summary>
        /// 获取补签后的连续签到天数
        /// </summary>
        /// <returns></returns>
        public T_VRUserSignIn GetSignInNum(DateTime date, int userid)
        {
            sb.Clear();
            sb.Append($@"with t1 as(select distinct createId,CONVERT(date,sigDate)ymd
            from T_VRUserSignIn where createId={userid}),
            t2 as( 
            select createId,ymd,ROW_NUMBER() over (partition by createId order by ymd)num, 
                   dateadd(day,-(ROW_NUMBER() over (partition by createId order by ymd)),ymd)sub_date
            from t1 
            )
            select createId,min(ymd)as startime ,max(ymd)as sigDate,count(*)as sigcomDay
            from t2 
            group by createId,sub_date
            having count(*)>=1");
            if (date != null)
            {
                sb.Append($@" and MAX(ymd)=CONVERT(date,'{date}')");
            }
            else
            {
                sb.Append($@" order by max(ymd) desc");
            }
            return SqlHelper.ExecuteList<T_VRUserSignIn>(sb.ToString()).FirstOrDefault();
        }

        /// <summary>
        /// 获取数据字典列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputGetDataDictList> GetDataDictList(InputGetDataDictList param)
        {
            sb.Clear();
            sb.Append($@"select code_name as name,code_value as code,code_type as cValue,numValue as qValue,code_note as note
    from T_ItemCodeMenum where item_id=(select item_id from T_ItemCode where item_code='{param.dictType}')");
            if (!string.IsNullOrEmpty(param.dictCode))
                sb.Append($@" and code_value='{param.dictCode}'");
            return SqlHelper.ExecuteList<OutputGetDataDictList>(sb.ToString());
        }
    }
}
